package cn.afterturn.easypoi.test.excel.read;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import org.apache.commons.lang3.StringUtils;
import org.junit.Assert;
import org.junit.Test;

import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @author yangyn
 * @version 1.0
 */
public class EastDataDictionary {

    @Test
    public void create() {

        ImportParams params = new ImportParams();
        params.setTitleRows(0);
        params.setHeadRows(1);
        params.setStartSheetIndex(0);
        // 读取表定义
//        String pathname = "D:\\工作文件\\02. 项目管理\\15. EAST\\数据库结构设计.xlsx";
//        String savePath = "D:\\工作文件\\02. 项目管理\\15. EAST\\creata_east.sql";

        String pathname = "D:\\工作文件\\02. 项目管理\\17. 沈阳人行课题\\数据库结构设计20190507.xlsx";
        String savePath = "D:\\工作文件\\02. 项目管理\\17. 沈阳人行课题\\creata_east.sql";

        List<Map<String, String>> tableDefList = ExcelImportUtil.importExcel(new File(pathname), Map.class, params);

        // 读取表列定义
        params.setStartSheetIndex(1);
        List<Map<String, String>> tableColsDefList = ExcelImportUtil.importExcel(new File(pathname), Map.class, params);
        // 根据表名进行分组
        final Map<Object, List<Map<String, String>>> tableColsMap = tableColsDefList.stream().collect(Collectors.groupingBy((map) -> map.get("表名称") == null ? "" : map.get("表名称")));

        StringBuffer sqlsb = new StringBuffer();

        for (Map<String, String> tableMap : tableDefList) {
            String tableName = tableMap.get("名称");
            String tableDesc = tableMap.get("描述");
            if (StringUtils.isBlank(tableName)) {
                continue;
            }
            // 表注释
            sqlsb.append("-- ").append(tableDesc).append("\n");
            sqlsb.append("CREATE TABLE ").append(tableName.toLowerCase()).append(" (\n");
            final List<Map<String, String>> cols = tableColsMap.get(tableName);
            List<String> pkList = new LinkedList<>();
            for (Map<String, String> col : cols) {
                String colName = StringUtils.lowerCase(col.get("字段名称"));
                String colType = col.get("字段类型");
                String colDesc = col.get("字段描述");
                String isPk = col.get("是否主键");
                // 添加字段
                sqlsb.append("    ").append(colName).append(" ").append(colType);
                // 记录主键
                if ("PK".equals(isPk)) {
                    // 主键设置为非空
                    sqlsb.append(" NOT NULL");
                    pkList.add(colName);
                }
                // 添加注释
                sqlsb.append(" COMMENT '").append(colDesc).append("'");
                sqlsb.append(", \n");
            }
            // 添加主键
            sqlsb.append("    PRIMARY KEY (").append(StringUtils.join(pkList.toArray(), ",")).append(" )\n");
            // 添加存储引擎、默认编码等
            sqlsb.append(")  ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='").append(tableDesc).append("'; \n");
        }

        final String sql = sqlsb.toString();
        System.out.println(sql);
        try {

            Files.write(Paths.get(savePath), sql.getBytes());
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}
